package sft.BancodeDados;

import java.sql.*;
import java.util.*;
import sft.Logica.Insumos;

public class Query {
    private static Connection conexao;

    public Query() throws SQLException {
        Query.conexao = new CriaConexao().getConnection();
    }
    
    /**
     *
     * @param l1 
     * @param criterio 
     * Digite 1 para pesquisar por nome e null para selecionar toda tabela
     * @return
     * @throws SQLException
     */
    public List<Insumos> getList(int criterio, Insumos l1) throws SQLException{
        if(conexao.isClosed()){
            conexao = new CriaConexao().getConnection();
            System.out.println("Recriou Conexao!");
        }
        
        PreparedStatement stmt = conexao.prepareStatement(l1.getSql());
        switch (criterio) {
            case 1:
        stmt.setString(1, "%" + l1.getNome() + "%");
                break;
            default:
                break;
        }        
        return Pesquisa(stmt);
    }

    private List<Insumos> Pesquisa(PreparedStatement stmt) throws SQLException {
        ResultSet rs = stmt.executeQuery();

        List<Insumos> list = new ArrayList<>();
        
            while (rs.next()) {
                Insumos l = new Insumos();
                l.setId(rs.getInt(1));
                l.setNome(rs.getString(2));
                try {
                    l.setUser(rs.getString(3));
                } catch (Exception e) {
                }
                try {
                    l.setCod(rs.getInt(3));
                } catch (Exception ex) {
                }
                list.add(l);
            }
        
        
        stmt.close();
        rs.close();
        conexao.close();
        return list;
    }
    
     public List<Insumos> getConvenios() throws SQLException{
         if (conexao.isClosed()) {
             conexao = new CriaConexao().getConnection();
             System.out.println("Recriou Conexao!");
         }

         PreparedStatement stmt = conexao.prepareStatement("select * from sft_convenios");
         
         ResultSet rs = stmt.executeQuery();
         
         List<Insumos> list = new ArrayList<>();
         
         while (rs.next()) {             
             Insumos i = new Insumos();
             i.setId(rs.getInt(1));
             i.setCod(rs.getInt(2));
             i.setNome(rs.getString(3));
             i.setFk_conv(rs.getInt(4));
             list.add(i);
         }
         
         stmt.close();
         rs.close();
         conexao.close();
         return list;
     }
}